#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
部署交易记录触发器
"""

from app import app, db
from models.models import User, BankCard, Transaction, UserAuth, DepositBusiness
import pymysql
from config import Config
import re

def parse_database_uri():
    """解析数据库连接字符串"""
    uri = Config.SQLALCHEMY_DATABASE_URI
    pattern = r'mysql\+pymysql://([^:]+):([^@]+)@([^/]+)/(.+)'
    match = re.match(pattern, uri)
    if match:
        return {
            'user': match.group(1),
            'password': match.group(2),
            'host': match.group(3),
            'database': match.group(4)
        }
    else:
        raise ValueError("无法解析数据库连接字符串")

def create_transaction_triggers():
    """创建交易记录触发器"""
    try:
        # 解析数据库配置
        db_config = parse_database_uri()
        
        # 连接数据库
        connection = pymysql.connect(
            host=db_config['host'],
            user=db_config['user'],
            password=db_config['password'],
            database=db_config['database'],
            charset='utf8mb4'
        )
        
        with connection.cursor() as cursor:
            # 删除已存在的触发器
            cursor.execute("DROP TRIGGER IF EXISTS after_card_balance_update")
            cursor.execute("DROP TRIGGER IF EXISTS after_transfer_transaction")
            
            # 创建银行卡余额变更触发器
            balance_trigger_sql = """
            CREATE TRIGGER after_card_balance_update
            AFTER UPDATE ON bank_card
            FOR EACH ROW
            BEGIN
                DECLARE transaction_type VARCHAR(10);
                DECLARE transaction_amount FLOAT;
                
                -- 只有当余额发生变化时才记录交易
                IF NEW.balance != OLD.balance THEN
                    -- 计算交易金额
                    SET transaction_amount = ABS(NEW.balance - OLD.balance);
                    
                    -- 判断交易类型
                    IF NEW.balance > OLD.balance THEN
                        SET transaction_type = 'deposit';
                    ELSE
                        SET transaction_type = 'withdraw';
                    END IF;
                    
                    -- 插入交易记录
                    INSERT INTO transaction (card_id, type, amount, target_card, timestamp)
                    VALUES (NEW.id, transaction_type, transaction_amount, NULL, NOW());
                    
                    -- 记录系统日志
                    INSERT INTO system_log (action, table_name, record_id, description, created_at)
                    VALUES (
                        'AUTO_TRANSACTION', 
                        'bank_card', 
                        NEW.id, 
                        CONCAT('自动记录', 
                            CASE transaction_type 
                                WHEN 'deposit' THEN '存款' 
                                WHEN 'withdraw' THEN '取款' 
                                ELSE transaction_type 
                            END, 
                            '交易: 卡号 ', NEW.card_number, ', 金额 ', transaction_amount, ' 元'
                        ), 
                        NOW()
                    );
                END IF;
            END
            """
            cursor.execute(balance_trigger_sql)
            print("✅ 银行卡余额变更触发器创建成功")
            
            # 创建转账交易记录触发器
            transfer_trigger_sql = """
            CREATE TRIGGER after_transfer_transaction
            AFTER INSERT ON transaction
            FOR EACH ROW
            BEGIN
                -- 如果是转账交易，需要特殊处理
                IF NEW.type = 'transfer' AND NEW.target_card IS NOT NULL THEN
                    -- 记录转账日志
                    INSERT INTO system_log (action, table_name, record_id, description, created_at)
                    VALUES (
                        'TRANSFER_RECORD', 
                        'transaction', 
                        NEW.id, 
                        CONCAT('转账交易记录: 从卡 ', 
                            (SELECT card_number FROM bank_card WHERE id = NEW.card_id), 
                            ' 到卡 ', NEW.target_card, 
                            ', 金额 ', NEW.amount, ' 元'
                        ), 
                        NOW()
                    );
                END IF;
            END
            """
            cursor.execute(transfer_trigger_sql)
            print("✅ 转账交易记录触发器创建成功")
            
            connection.commit()
            
            # 验证触发器
            cursor.execute("SHOW TRIGGERS LIKE 'bank_card'")
            bank_card_triggers = cursor.fetchall()
            print(f"📋 银行卡表触发器: {len(bank_card_triggers)} 个")
            
            cursor.execute("SHOW TRIGGERS LIKE 'transaction'")
            transaction_triggers = cursor.fetchall()
            print(f"📋 交易表触发器: {len(transaction_triggers)} 个")
            
    except Exception as e:
        print(f"❌ 创建交易记录触发器失败: {e}")
    finally:
        if 'connection' in locals():
            connection.close()

def test_transaction_triggers():
    """测试交易记录触发器"""
    try:
        with app.app_context():
            # 获取一个测试银行卡
            test_card = BankCard.query.first()
            if not test_card:
                print("❌ 没有找到测试银行卡")
                return
            
            print(f"🧪 测试银行卡: {test_card.card_number}, 当前余额: {test_card.balance}")
            
            # 测试存款触发器
            original_balance = test_card.balance
            test_card.balance += 100.0
            db.session.commit()
            
            # 检查是否自动创建了交易记录
            latest_transaction = Transaction.query.filter_by(card_id=test_card.id).order_by(Transaction.id.desc()).first()
            if latest_transaction and latest_transaction.type == 'deposit' and latest_transaction.amount == 100.0:
                print("✅ 存款触发器测试成功")
            else:
                print("❌ 存款触发器测试失败")
            
            # 恢复余额
            test_card.balance = original_balance
            db.session.commit()
            print("🔄 测试余额已恢复")
            
    except Exception as e:
        print(f"❌ 测试交易记录触发器失败: {e}")

if __name__ == "__main__":
    print("🚀 开始部署交易记录触发器...")
    
    # 创建交易记录触发器
    create_transaction_triggers()
    
    # 测试触发器
    print("\n🧪 测试交易记录触发器...")
    test_transaction_triggers()
    
    print("\n✅ 交易记录触发器部署完成！") 